﻿using System;
using System.Collections.Generic;
using DB.Core.Models;
using Furion;
using Furion.DynamicApiController;
using Furion.FriendlyException;
using Microsoft.AspNetCore.Authorization;
using Microsoft.AspNetCore.Mvc;
using Newtonsoft.Json;
using PMBF.Application.Common;
using PMBF.Core.Enums;
using PMBF.Core.ViewModels;
using SqlSugar;
using System.Security.Claims;
using System.Data;
using System.Linq;
using PMBF.Core.Handler;
using PMBF.Core.Plugins;
using System.IO;

namespace PMBF.Application.Api
{
    [AppAuthorize, ApiDescriptionSettings("SQL动态报表管理")]
    [UserApiAuthenticationActionFilter]
    public class SqlReportsService : IDynamicApiController
    {
        private readonly ISqlSugarRepository repository;
        private readonly SqlSugarClient db;
        private readonly int ModelId = 38;

        /// <summary>
        /// 动态报表
        /// </summary>
        /// <param name="sqlSugarRepository"></param>
        public SqlReportsService(ISqlSugarRepository sqlSugarRepository)
        {
            repository = sqlSugarRepository;
            db = repository.Context;
        }

        #region 查询列表、编辑、保存、禁用、删除、导出

        /// <summary>
        /// 查询列表
        /// </summary>
        /// <param name="_query"></param>
        /// <returns></returns>
        public Model Post(ApiModelsQuerys _query)
        {
            return new ModelsService(repository).Model(ModelId, _query);
        }

        /// <summary>
        /// 新增编辑，获取信息
        /// </summary>
        /// <param name="_id"></param>
        /// <returns></returns>
        public dynamic GetInfo(int _id)
        {
            var page = new ModelsService(repository).GetEditData(ModelId);

            if (_id != 0)
            {
                var tmpuser = db.Queryable<SqlReportModel>().Where(n => n.ID == _id).First();
                page.Page.Title = "编辑SQL报表";
                page.DataSource = tmpuser;
            }
            else
            {
                page.Page.Title = "新增SQL报表";
                //page.DataSource = new SqlReportModel();
            }

            return page;
        }

        /// <summary>
        /// 数据保存
        /// </summary>
        /// <param name="model"></param>
        /// <returns></returns>
        public dynamic PostSave(SqlReportModel model)
        {
            bool isok = false;

            //判断model.RouteName 不许重复
            SqlReportModel isHave = null;
            if (model.ID == 0)
            {
                isHave = db.Queryable<SqlReportModel>().Where(i => i.RouteName == model.RouteName).First();
            }
            else
            {
                isHave = db.Queryable<SqlReportModel>().Where(i => i.RouteName == model.RouteName && i.ID != model.ID).First();
            }

            if (isHave != null)
            {
                return new { Success = false, message = "RouteName 已存在，不允许重复！！！" };
            }

            if (model.ID == 0)
            {
                model.CreateTime = DateTime.Now;
                model.CreateUserID = Convert.ToInt32(App.User?.FindFirstValue("UserID"));
                model.DeleteFlag = 0;
                var tmpmodel = db.Insertable(model).ExecuteReturnEntity();
                model.ID = tmpmodel.ID;
                isok = model.ID > 0;
            }
            else
            {
                model.ModifyTime = DateTime.Now;
                model.ModifyUserID = Convert.ToInt32(App.User?.FindFirstValue("UserID"));
                isok = db.Updateable(model).IgnoreColumns(ignoreAllNullColumns: true).IgnoreColumns("CreateTime", "CreateUserID").ExecuteCommand() > 0;
            }
            if (isok == false)
            {
                throw Oops.Oh(SystemErrorCodes.E0010);
            }

            return new { Success = true, message = "保存成功" };
        }


        /// <summary>
        /// 正常数据，将数据删除标志设置成已删除
        /// </summary>
        /// <returns></returns>
        public dynamic PostDisable(ApiPostExtendModel data)
        {
            int userId = Convert.ToInt32(App.User?.FindFirstValue("UserID"));
            foreach (int id in data.ids)
            {
                db.Updateable<SqlReportModel>().SetColumns(n => new SqlReportModel()
                {
                    DeleteFlag = 1,
                    DeleteTime = DateTime.Now,
                    DeleteUserID = userId

                }).Where(n => n.ID == id).ExecuteCommand();
            }

            return new { Success = true, message = "删除成功" };
        }


        /// <summary>
        /// 有数据删除标志设置的直接删除
        /// </summary>
        /// <returns></returns>
        public dynamic PostDelete(ApiPostExtendModel data)
        {
            foreach (int id in data.ids)
            {
                db.Deleteable<SqlReportModel>().Where(n => n.ID == id && n.DeleteFlag == 1).ExecuteCommand();

                //目录暂未处理
            }

            return new { Success = true, message = "删除成功" };
        }

        /// <summary>
        /// 有数据删除标志设置的恢复
        /// </summary>
        /// <returns></returns>
        public dynamic PostRestore(ApiPostExtendModel data)
        {
            int userId = Convert.ToInt32(App.User?.FindFirstValue("UserID"));
            foreach (int id in data.ids)
            {
                db.Updateable<SqlReportModel>().SetColumns(n => new SqlReportModel() { DeleteFlag = 0, ModifyTime = DateTime.Now, ModifyUserID = userId }).Where(n => n.ID == id && n.DeleteFlag == 1).ExecuteCommand();
            }

            return new { Success = true, message = "恢复成功" };
        }

        #endregion



        /// <summary>
        /// 获取ReportDesign Json
        /// </summary>
        /// <param name="id"></param>
        /// <returns></returns>
        public ApiSQLReportDesignRoot GetReportDesign(int id)
        {
            SqlReportModel rptModel = db.Queryable<SqlReportModel>().Where(i => i.ID == id).First() ?? throw Oops.Oh(SystemErrorCodes.E0003);

            ApiSQLReportDesignRoot rs = new();

            rs.RouteName = rptModel.RouteName;

            rs.MenuName = rptModel.ReportName;

            rs = (rptModel.DesignData == null) ? rs : JsonConvert.DeserializeObject<ApiSQLReportDesignRoot>(rptModel.DesignData);

            return rs;

        }

        /// <summary>
        /// 保存 ReportDesign Json
        /// </summary>
        /// <param name="model"></param>
        /// <param name="id"></param>
        /// <returns></returns>
        public object PostReportDesign([FromQuery] int id, ApiSQLReportDesignRoot model)
        {
            //判断model.RouteName 不许重复
            var isHave = db.Queryable<SqlReportModel>().Where(i => i.RouteName == model.RouteName && i.ID != id).First();

            if (isHave != null)
            {
                return new { Success = false, message = "RouteName 已存在，不允许重复！！！" };
            }

            SqlReportModel rptModel;

            rptModel = db.Queryable<SqlReportModel>().Where(i => i.ID == id).First() ?? throw Oops.Oh(SystemErrorCodes.E0003);

            rptModel.ReportName = model.MenuName;

            rptModel.RouteName = model.RouteName;

            rptModel.DesignData = JsonConvert.SerializeObject(model);

            #region antd table fields => PMFApiRoot

            var listData = new PMFApiRoot();

            Layout layout = new();

            var columns = new List<TableColumn>();

            var sqlfields = db.Ado.GetDataTable(model.SqlText + " where 1=2 " + model.GroupSqlText).Columns ?? throw Oops.Oh(SystemErrorCodes.E0010); ;

            foreach (DataColumn col in sqlfields)
            {
                TableColumn column = new TableColumn();

                column.Key = col.Caption;

                column.Title = col.Caption;

                column.DataIndex = col.Caption;

                column.Sorter = false; //此处配置不好用

                //other table column setting .....

                columns.Add(column);
            }

            layout.TableColumn = columns;

            var tabletoolbar = new List<TableToolBar>();

            foreach (var item in model.TableToolbar)
            {
                var btn = new TableToolBar();

                btn.Component = "button";

                btn.Action = item.Action;

                btn.Text = item.Title;

                btn.Type = item.Type;

                btn.Uri = item.Uri;

                tabletoolbar.Add(btn);
            }

            layout.TableToolBar = tabletoolbar;

            listData.Layout = layout;

            listData.Page = new Page { Title = model.MenuName, TableName = model.RouteName };

            rptModel.ListData = JsonConvert.SerializeObject(listData);

            #endregion

            #region antd table search form info => List<TableColumn>

            var searchFormFields = new List<ApiPageModelViewModel.Fields>();

            foreach (var item in model.Fields)
            {
                var field = new ApiPageModelViewModel.Fields
                {
                    Title = item.Title,

                    Key = item.Name,

                    DataIndex = item.Name,

                    Type = item.Type,

                    IsLike = item.IsLike,
                };

                //select Code as value,Name as title from ItemList where TypeCode='DataStatus'

                if (item.Type == "selects" || item.Type == "select" || item.Type == "radio" || item.Type == "switch")
                {
                    field.Data = db.Ado.SqlQuery<dynamic>(item.Data).ToList();
                }
                else if (item.Type == "tree")
                {
                    List<dynamic> treedatas = new List<dynamic>();
                    var tmpdata = db.Ado.SqlQuery<dynamic>(item.Data).ToList();
                    field.Data = CreateTreeChild(tmpdata, 0);
                    field.Sql = "";
                }
                else if (item.Type == "parent")
                {
                    List<dynamic> treedatas = new List<dynamic>();
                    var tmpdata = db.Ado.SqlQuery<dynamic>(item.Data).ToList();
                    tmpdata.Add(new
                    {
                        ID = 0,
                        parentId = -1,
                        title = "根级"
                    });
                    field.Data = CreateTreeChild(tmpdata, -1);
                    field.Sql = "";
                }
                else
                {
                    field.Sql = "";
                }

                searchFormFields.Add(field);
            }

            rptModel.SearchData = JsonConvert.SerializeObject(searchFormFields);

            #endregion

            rptModel.ModifyTime = DateTime.Now;

            rptModel.ModifyUserID = Convert.ToInt32(App.User?.FindFirstValue("UserID"));

            db.Updateable(rptModel).ExecuteCommand();


            #region 初始化菜单表
            MenuRule _rptmenu = db.Queryable<MenuRule>().Where(i => i.Path == "/basic-sqlrpt").First();

            if (_rptmenu == null)
            {
                MenuRule rptMenu = new()
                {
                    ParentMenuRuleID = 0,

                    Name = "报表管理(Auto)",

                    Icon = "icon-barchart",

                    Path = "/basic-sqlrpt",

                    HideChildrenInMenu = 0,

                    HideInMenu = 0,

                    FlatMenu = 0,

                    HttpMethod = "post",

                    CreateUserID = Convert.ToInt32(App.User?.FindFirstValue("UserID")),

                    CreateTime = DateTime.Now
                };

                _rptmenu = db.Insertable(rptMenu).ExecuteReturnEntity();
            }

            MenuRule _subrptmenu = db.Queryable<MenuRule>().Where(i => i.Path.Contains("/basic-sqlrpt/" + model.RouteName + "/")).First();

            if (_subrptmenu == null)
            {
                MenuRule newMenu = new();

                newMenu.ParentMenuRuleID = _rptmenu.ID;

                newMenu.Name = model.MenuName;

                newMenu.Icon = "icon-barchart";

                newMenu.Api = "/api/sql-reports/show-report/";

                newMenu.Path = "/basic-sqlrpt/" + model.RouteName + "/";

                newMenu.HideChildrenInMenu = 0;

                newMenu.HideInMenu = 0;

                newMenu.FlatMenu = 0;

                newMenu.HttpMethod = "post";

                newMenu.CreateUserID = Convert.ToInt32(App.User?.FindFirstValue("UserID"));

                newMenu.CreateTime = DateTime.Now;

                var rtmenu = db.Insertable(newMenu).ExecuteReturnEntity();

                rtmenu.Path += id.ToString();

                db.Updateable(rtmenu).ExecuteCommand();
            }
            #endregion

            return new { Success = true, message = "保存成功！" };
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="tmpdata"></param>
        /// <param name="parentId"></param>
        /// <returns></returns>
        private dynamic CreateTreeChild(List<dynamic> tmpdata, int parentId)
        {
            List<dynamic> treedatas = new List<dynamic>();

            var childsdata = tmpdata.Where(n => n.parentId == parentId).ToList();

            foreach (var t in childsdata)
            {
                List<dynamic> children = new List<dynamic>();

                if (tmpdata.Where(n => n.parentId == t.ID).ToList().Count > 0)
                {
                    children = CreateTreeChild(tmpdata, t.ID);
                }

                treedatas.Add(new
                {
                    key = t.ID,
                    value = t.ID,
                    parentId = t.parentId,
                    title = t.title,
                    children = children
                });
            }

            return treedatas;
        }

        /// <summary>
        /// 测试
        /// </summary>
        /// <returns></returns>
        public dynamic GetTest()
        {
            var sql = @"select u.id as ID, u.UserName as 用户名 , g.GroupName as 组名, ug.CreateTime as 创建时间 from UserGroup ug
                left join [Group] g on(g.ID = ug.GroupID)
                left join [User] u on(u.ID = ug.UserID) ";

            //先分页再转DataTable  where里的ID是select以后的值
            var dt = db.SqlQueryable<object>(sql).Where("id=@id", new { id = 1 }).ToDataTablePage(1, 2);

            var js = JsonHelper.DataTableToJSON(dt);

            return js;
        }

        /// <summary>
        /// 测试
        /// </summary>
        /// <returns></returns>
        public dynamic GetDataTable()
        {
            var sql = @"select u.id as ID, u.UserName as 用户名 , g.GroupName as 组名, ug.CreateTime as 创建时间 from UserGroup ug
                left join [Group] g on(g.ID = ug.GroupID)
                left join [User] u on(u.ID = ug.UserID) ";

            //先分页再转DataTable  where里的ID是select以后的值
            var dt = db.SqlQueryable<object>(sql).Where("id=@id", new { id = 1 }).ToDataTablePage(1, 2);

            return dt;
        }

        /// <summary>
        /// 报表展示页
        /// </summary>
        /// <param name="id"></param>
        /// <param name="_query"></param>
        /// <returns></returns>
        public PMFApiRoot PostShowReport([FromQuery] int id, ApiModelsQuerys _query)
        {
            var _id = id;

            SqlReportModel rptModel;

            rptModel = db.Queryable<SqlReportModel>().Where(i => i.ID == _id).First() ?? throw Oops.Oh(SystemErrorCodes.E0003);

            var designmode = JsonConvert.DeserializeObject<ApiSQLReportDesignRoot>(rptModel.DesignData);

            PMFApiRoot listJson = JsonConvert.DeserializeObject<PMFApiRoot>(rptModel.ListData);

            listJson.Layout.SearchColumn = JsonConvert.DeserializeObject<List<TableColumn>>(rptModel.SearchData);

            //根据_query 处理数据并更新 listJson.DataSource
            //......

            #region 构建查询条件

            string orderby = "";

            for (int i = 0; i < _query.sort.Length; i++)
            {
                orderby += " " + _query.sort[i] + " " + _query.order[i] + " ,";
            }

            orderby = orderby == "" ? "id" : orderby.Substring(0, orderby.Length - 1);

            string deleteFlag = "0";

            var whereModels = new List<ConditionalModel>();

            if (!string.IsNullOrEmpty(_query.queryfields))
            {
                dynamic querys = JsonConvert.DeserializeObject(_query.queryfields);
                foreach (var m in querys)
                {
                    //判断search field isLike 是否模糊查询
                    bool isLike = designmode.Fields.Find(i => i.Name == m.Name).IsLike ?? false;

                    if (m.Value == null)
                    {
                        continue;
                    }

                    if (m.Name == "id")
                    {
                        whereModels.Add(new ConditionalModel { FieldName = "id", ConditionalType = ConditionalType.Equal, FieldValue = m.Value.ToString() });
                    }
                    else if (m.Name == "deleteFlag")
                    {
                        deleteFlag = m.Value.ToString();
                    }
                    else
                    {
                        var tmpfield = listJson.Layout.SearchColumn.FirstOrDefault(n => n.Key == m.Name.ToString());
                        if (tmpfield != null)
                        {
                            switch (tmpfield.Type)
                            {
                                case "datetime":
                                    whereModels.Add(new ConditionalModel { FieldName = m.Name.ToString(), ConditionalType = ConditionalType.GreaterThanOrEqual, FieldValue = m.Value.First.ToString() });
                                    whereModels.Add(new ConditionalModel { FieldName = m.Name.ToString(), ConditionalType = ConditionalType.LessThan, FieldValue = m.Value.Last.ToString() });
                                    break;
                                case "select":
                                case "tree":
                                case "radio":
                                case "switch":
                                    whereModels.Add(new ConditionalModel { FieldName = m.Name.ToString(), ConditionalType = ConditionalType.Equal, FieldValue = m.Value.ToString() });
                                    break;
                                default:
                                    whereModels.Add(new ConditionalModel { FieldName = m.Name.ToString(), ConditionalType = isLike ? ConditionalType.Like : ConditionalType.Equal, FieldValue = m.Value.ToString() });
                                    break;
                            }
                        }
                    }
                }
            }

            string wheresql = Object2SqlWhere(whereModels);

            var orimodel = JsonConvert.DeserializeObject<ApiSQLReportDesignRoot>(rptModel.DesignData);

            string allsql = orimodel.SqlText + " " + wheresql + " " + orimodel.GroupSqlText;

            string exportsql = orimodel.SqlText + " " + orimodel.GroupSqlText;

            #endregion
            DataTable dt;

            if (_query.export)
            {
                dt = db.SqlQueryable<object>(exportsql).ToDataTable();
            }
            else
            {
                //先分页再转DataTable  where里的ID是select以后的值
                int _pageindex = _query.page;

                int _pagesize = _query.per_page;

                int total = 0;

                dt = db.SqlQueryable<object>(allsql).ToDataTablePage(_pageindex, _pagesize, ref total);

                listJson.Meta = new Meta { Page = _pageindex, PerPage = _pagesize, Total = total };
            }

            listJson.DataSource = dt;

            return listJson;
        }

        private string Object2SqlWhere(List<ConditionalModel> list)
        {
            //{ FieldName = "deleteFlag", ConditionalType = ConditionalType.Equal, FieldValue = deleteFlag }

            string wheresql = " where 1=1 ";

            foreach (ConditionalModel item in list)
            {
                switch (item.ConditionalType)
                {
                    case ConditionalType.Equal:
                        {
                            wheresql += " and " + item.FieldName + " = " + " '"+item.FieldValue+"' ";
                            break;
                        }
                    case ConditionalType.Like:
                        {
                            wheresql += " and " + item.FieldName + " like " + " '%" + item.FieldValue + "%' ";
                            break;
                        }
                    case ConditionalType.GreaterThanOrEqual:
                        {
                            wheresql += " and " + item.FieldName + " >= " + " '" + item.FieldValue + "' ";
                            break;
                        }
                    case ConditionalType.LessThan:
                        {
                            wheresql += " and " + item.FieldName + " <= " + " '" + item.FieldValue + "' ";
                            break;
                        }
                    default:
                        break;
                }
            }

            return wheresql;
        }

        /// <summary>
        /// 导出
        /// </summary>
        /// <param name="id"></param>
        /// <param name="_query"></param>
        /// <returns></returns>
        [NonUnify]
        public IActionResult PostExport([FromQuery] int id, ApiModelsQuerys _query)
        {
            List<string> Exportpro = new List<string>();

            List<string> ExportTitles = new List<string>();

            //对应数据
            var model = PostShowReport(id, _query);

            var list = model.DataSource as DataTable;

            foreach (var m in model.Layout.TableColumn)
            {
                if (!Exportpro.Contains(m.Key))
                {
                    Exportpro.Add(m.Key);
                    ExportTitles.Add(m.Title);
                }
            }

            var tmpStream = ExcelHelper.DataTableToExcel(list, Exportpro, model.Page.Title);

            MemoryStream ms = new MemoryStream(tmpStream);

            ms.Position = 0;

            return new FileStreamResult(ms, "application/octet-stream") { FileDownloadName = "export.xls" };
        }

    }
}
